# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql


class MafwPipeline:
    def __init__(self):
        self.connect = pymysql.Connect(
            # host='localhost',
            # port=3306,
            # user='root',
            # password='tops',
            # db='hdj',
            # charset='utf8',
            host='172.22.113.14',
            port=9306,
            user='root',
            password='123456',
            db='DAVEDB0006',
            charset='utf8',

        )
        self.cursor = self.connect.cursor()
        print('连接上mysql数据库')

    def process_item(self, item, spider):
        try:
            self.connect.ping(reconnect=True)
            sql2 = 'select * from travel_details_data where name="%s"and country="%s" and city="%s" ' %(item['name'],item['country'],item['city'])
            if not self.cursor.execute(sql2):
                sql = """ INSERT INTO travel_details_data (name, tour_type, country, city,english_name,GPS,phone,traffic,fare,opening_hours,source_site,verify,alter_name,alter_time,del_flag,user_id)
                        VALUES ("%s",'%s', '%s','%s',"%s",'%s','%s',"%s",'%s','%s','%s','%s','%s','%s','%s','%s')""" % (
                    item['name'], item['tour_type'], item['country'],
                    item['city'], item['english_name'], item['GPS'],
                    item['phone'], item['traffic'], item['fare'],
                    item['opening_hours'], item['source_site'],
                    item['verify'], item['alter_name'], item['alter_time'],
                    item['del_flag'], item['user_id'])
                try:
                    self.connect.ping(reconnect=True)
                    self.cursor.execute(sql)
                    print('成功插入', self.cursor.rowcount, '条数据', sql)
                    self.connect.commit()
                    return item
                except:
                    self.connect.commit()

                self.connect.commit()
            else:
                print(f'已经有数据: {item["name"]}')
                self.connect.commit()
        except Exception as e:
            print('哟错误:',e)

    def close_spider(self, spider):
        # 关闭游标和连接
        self.cursor.close()
        self.connect.close()
